package com.sychip.fhc.app.data.source.dao


import androidx.room.Dao
import androidx.room.Query
import androidx.room.Upsert
import com.sychip.fhc.app.data.source.entity.WsnDeviceRssiEntity
import kotlinx.coroutines.flow.Flow

const val TBL_WsnDeviceRssi = "wsnDeviceRssi"
/**
 * Data Access Object for the wsnDevice table.
 */
@Dao
interface WsnDeviceRssiDao {

    /**
     * Observes list of tasks.
     *
     * @return all tasks.
     */
    @Query("SELECT * FROM $TBL_WsnDeviceRssi WHERE usbId = :usbId")
    fun observeAll(usbId: String): Flow<List<WsnDeviceRssiEntity>>

    @Query("SELECT t1.usbId, t1.fromDevId, t1.toDevId, t1.rssi, t1.dateTime FROM $TBL_WsnDeviceRssi t1 INNER JOIN ( SELECT usbId, fromDevId, toDevId, MAX(dateTime) AS maxDateTime FROM $TBL_WsnDeviceRssi GROUP BY usbId, fromDevId, toDevId ) t2 ON t1.usbId = t2.usbId AND t1.fromDevId = t2.fromDevId AND t1.toDevId = t2.toDevId AND t1.dateTime = t2.maxDateTime  AND t1.usbId = :usbId")
    fun observeLastRssi(usbId: String): Flow<List<WsnDeviceRssiEntity>>

    @Query("SELECT t1.usbId, t1.fromDevId, t1.toDevId, t1.rssi, t1.dateTime FROM $TBL_WsnDeviceRssi t1 INNER JOIN ( SELECT usbId, fromDevId, toDevId, MIN(dateTime) AS minDateTime FROM $TBL_WsnDeviceRssi GROUP BY usbId, fromDevId, toDevId ) t2 ON t1.usbId = t2.usbId AND t1.fromDevId = t2.fromDevId AND t1.toDevId = t2.toDevId AND t1.dateTime = t2.minDateTime  AND t1.usbId = :usbId AND t1.datetime >= :dateTime")
    fun observeOldestDataFrom(usbId: String, dateTime:Long): Flow<List<WsnDeviceRssiEntity>>

    @Query("SELECT t1.usbId, t1.fromDevId, t1.toDevId, t1.rssi, t1.dateTime FROM $TBL_WsnDeviceRssi t1  WHERE t1.usbId = :usbId AND t1.datetime >= :dateTime Order By datetime ASC")
    fun observeAllDataFrom(usbId: String, dateTime:Long): Flow<List<WsnDeviceRssiEntity>>

    @Query("SELECT * FROM $TBL_WsnDeviceRssi WHERE usbId = :usbId and fromDevId = :fromDevId AND toDevId = :toDevId")
    fun observeRssiByFromToDev(usbId: String, fromDevId: String,toDevId: String): Flow<List<WsnDeviceRssiEntity>>

//    @Query("WITH sorted_data AS ( SELECT fromDevId, toDevId, COALESCE(rssi, 0) AS rssi, datetime, (ROW_NUMBER() OVER (PARTITION BY fromDevId, toDevId ORDER BY datetime) - 1) / :groupNum AS groupNum FROM $TBL_WsnDeviceRssi ) SELECT fromDevId, toDevId, groupNum, AVG(rssi) AS avgRssi FROM sorted_data GROUP BY fromDevId, toDevId, groupNum")
//    fun avgRssiGroupByNum(groupNum: Int): Flow<List<WsnRssiAvgDto>>



    /**
     * Observes a single bleDevice.
     *
     * @param devId the bleDevice id.
     * @return the bleDevice with devId.
     */
    @Query("SELECT * FROM $TBL_WsnDeviceRssi WHERE usbId = :usbId and fromDevId = :devId")
    fun observeById(usbId: String, devId: String): Flow<WsnDeviceRssiEntity>

    /**
     * Select all tasks from the tasks table.
     *
     * @return all tasks.
     */
    @Query("SELECT * FROM $TBL_WsnDeviceRssi WHERE usbId = :usbId")
    suspend fun getAll(usbId: String): List<WsnDeviceRssiEntity>


    @Query("SELECT t1.usbId, t1.fromDevId, t1.toDevId, t1.rssi, t1.dateTime FROM $TBL_WsnDeviceRssi t1 INNER JOIN ( SELECT usbId, fromDevId, toDevId, MAX(dateTime) AS maxDateTime FROM $TBL_WsnDeviceRssi GROUP BY usbId, fromDevId, toDevId ) t2 ON t1.usbId = t2.usbId AND t1.fromDevId = t2.fromDevId AND t1.toDevId = t2.toDevId AND t1.dateTime = t2.maxDateTime  AND t1.usbId = :usbId")
    fun getAllLastRssi(usbId: String): List<WsnDeviceRssiEntity>
    /**
     * Select a bleDevice by id.
     *
     * @param devId the bleDevice id.
     * @return the bleDevice with devId.
     */
    @Query("SELECT * FROM $TBL_WsnDeviceRssi WHERE usbId = :usbId AND fromDevId = :fromDevId AND toDevId = :toDevId AND dateTime >= :dateTime ORDER BY dateTime")
    suspend fun getByFromToAndTime(usbId: String, fromDevId: String, toDevId: String, dateTime: Long):List<WsnDeviceRssiEntity>
    /**
     * Select a bleDevice by id.
     *
     * @param devId the bleDevice id.
     * @return the bleDevice with devId.
     */
    @Query("SELECT * FROM $TBL_WsnDeviceRssi WHERE usbId = :usbId and toDevId = :devId")
    suspend fun getByToDevId(usbId: String, devId: String):List<WsnDeviceRssiEntity>

    /**
     * Insert or update a bleDevice in the database. If a bleDevice already exists, replace it.
     *
     * @param bleDevice the bleDevice to be inserted or updated.
     */
    @Upsert
    suspend fun upsert(bleDevice: WsnDeviceRssiEntity)

    /**
     * Insert or update tasks in the database. If a bleDevice already exists, replace it.
     *
     * @param tasks the tasks to be inserted or updated.
     */
    @Upsert
    suspend fun upsertAll(tasks: List<WsnDeviceRssiEntity>)


    /**
     * Delete a bleDevice by id.
     *
     * @return the number of tasks deleted. This should always be 1.
     */
    @Query("DELETE FROM $TBL_WsnDeviceRssi WHERE usbId = :usbId and fromDevId = :fromDevId")
    suspend fun deleteByFromId(usbId: String, fromDevId: String): Int
    /**
     * Delete a bleDevice by id.
     *
     * @return the number of tasks deleted. This should always be 1.
     */
    @Query("DELETE FROM $TBL_WsnDeviceRssi WHERE usbId = :usbId and toDevId = :toDevId")
    suspend fun deleteByToId(usbId: String, toDevId: String): Int

    /**
     * Delete all tasks.
     */
    @Query("DELETE FROM $TBL_WsnDeviceRssi WHERE usbId = :usbId")
    suspend fun deleteAll(usbId: String): Int

    @Query("DELETE FROM $TBL_WsnDeviceRssi WHERE datetime(dateTime) < datetime('now', 'localtime', '-' || :hours || ' hours')")
    suspend fun deleteDataBefore(hours: Int): Int
}
